/***
    Amrita ITEWS - Copyright (C) 2009 Amrita Vishwa Vidyapeetham, Amritapuri.
                                       (http://www.amrita.edu)
    ***************************************************************************
    This file is part of the Amrita ITEWS distribution.
    Amrita ITEWS is free software; you can redistribute it and/or modify it 
    under the terms of the GNU General Public License (version 2) as published
    by the Free Software Foundation AND MODIFIED BY the Amrita ITEWS exception.
    ***NOTE*** The exception to the GPL is included to allow you to distribute
    a combined work that includes Amrita ITEWS without being obliged to provide
    the source code for proprietary components outside of the Amrita ITEWS
    software. Amrita ITEWS is distributed in the hope that it will be useful, 
    but WITHOUT ANY WARRANTY; without even the implied warranty of 
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General
    Public License for more details. You should have received a copy of the GNU
    General Public License and the Amrita ITEWS license exception along with 
    Amrita ITEWS if not then it can be viewed here: 
    http://itews.amrita.ac.in/license.html.

    Documentation, latest information, license and contact details are at:
    http://itews.amrita.ac.in/

    Amrita ITEWS source code can be found at:
    http://code.google.com/p/itews

    The initial code-base of Amrita ITEWS was developed by Amrita Vishwa 
    Vidyapeetham as part of the project titled,"Intelligent & Interactive 
    Telematics using Emerging Wireless Technologies for Transport Systems 
    (ITEWS)" sponsored by Technology Information Forecasting and Assessment 
    Council (TIFAC), India.
***/

package tdcsinfoservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import org.knopflerfish.service.log.LogRef;
import org.osgi.service.event.Event;

public class POIInfoDatabaseUpdater 
{
	private static final String POI_LIST = "poi_list";
	private static final String CONTROL_PARAM = "control_param";
	private static final String POI_CODE = "poi_code";
	private static final String POI_LAT = "poi_latitude";
	private static final String POI_LONG = "poi_longitude";
	private static final String POI_DETAILS_ = "poi_details_";
	private static final String POI_NAME_ = "poi_name_";
	private static final String DELETE_OPT = "delete";
	private DBConnectionPool dbConnectionPool ;
	private TDCSInfoServiceConfig config;
	private  LogRef log;
	private String pointsOfInterestQuery = "SELECT id from points_of_interest where code=?";
	private String pointsOfInterestInsert = "INSERT INTO points_of_interest VALUES(default, ?, ?, ?)";
	private String pointsOfInterestUpdate = "UPDATE points_of_interest SET poi_lat=?, poi_long=? WHERE code=?";
	private String pointsOfInterestDelete = "DELETE FROM nodes WHERE node_code=?";
	private String pointsOfInterestDescriptorsQuery = "SELECT id FROM points_of_interest_descriptors WHERE point_of_interest_code=? AND lang_code=?";
	private String pointsOfInterestDescriptorsInsert = "INSERT INTO points_of_interest_descriptors VALUES(default, ?, ?, ?, ?)";
	private String pointsOfInterestDescriptorsUpdate = "UPDATE points_of_interest_descriptors SET name=?, details=? WHERE point_of_interest_code=? AND lang_code=?";
	
	public POIInfoDatabaseUpdater()
	{
		try
		{
			log = new LogRef(Activator.bc);
			config = TDCSInfoServiceConfig.getConfig();
			dbConnectionPool = DBConnectionPool.getInstance(config.getJdbcConnectionURI());
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	public synchronized void updatePOIInfoDatabase(Event event)
	{
		Connection conn = null;
		PreparedStatement preparedStmt1 = null;
		PreparedStatement preparedStmt2 = null;
		PreparedStatement preparedStmt3 = null;
		PreparedStatement preparedStmt4 = null;
		PreparedStatement preparedStmt5 = null;
		PreparedStatement preparedStmt5A = null;
		PreparedStatement preparedStmt6 = null;
		PreparedStatement preparedStmt6A = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		try 
		{
			Object poiListObj = event.getProperty(POI_LIST);
			if (poiListObj == null || ((ArrayList) poiListObj).size() == 0)
			{
				throw new Exception("POI Info database update aborted. POI List empty or not available in feed");
			}
			ArrayList poiList = (ArrayList) poiListObj;
			conn = dbConnectionPool.getConnection();
			conn.setAutoCommit(false);
			for (int i=0; i<poiList.size(); i++)
			{
				String controlParam=null, poiCode=null;
				double 	poiLat=999d, poiLong=999d;
				Object attr=null;
				HashMap poiMember = (HashMap) poiList.get(i);
				attr = poiMember.get(CONTROL_PARAM);
				if (attr != null) controlParam = (String) attr;
				attr = poiMember.get(POI_CODE);
				if (attr != null) poiCode = (String) attr;
				attr = poiMember.get(POI_LAT);
				if (attr != null)
				{
					if (attr instanceof Double)
						poiLat = (Double) attr;
					else if (attr instanceof Integer)
						poiLat =  (Integer) attr;
				}
				attr = poiMember.get(POI_LONG);
				if (attr != null)
				{
					if (attr instanceof Double)
						poiLong = (Double) attr;
					else if (attr instanceof Integer)
						poiLong = (Integer) attr;
				}
				if (poiCode != null && controlParam != null && controlParam.trim().equals(DELETE_OPT))
				{
					/*
					 * Dependent tables points_of_interest_descriptors and points_of_interest_enroute
					 * will also be cascade deleted
					 */
					preparedStmt1 = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestDelete);
					preparedStmt1.setString(1, poiCode);
					preparedStmt1.executeUpdate();
					preparedStmt1.close();
					preparedStmt1 = null;
				}
				else if (poiCode!=null && poiLat!=999d && poiLong!=999d)
				{
					preparedStmt2 = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestQuery);
					preparedStmt2.setString(1, poiCode);
					rs1 = preparedStmt2.executeQuery();
					if (rs1.next() == false) //if points_of_interest table insertion is to be done
					{
						preparedStmt3 = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestInsert);
						preparedStmt3.setString(1, poiCode);
						preparedStmt3.setDouble(2, poiLat);
						preparedStmt3.setDouble(3, poiLong);
						preparedStmt3.executeUpdate();
						preparedStmt3.close();
						preparedStmt3 = null;
						
						String[] languages = config.getLanguages();
						for (int j=0; j< languages.length; j++)
						{
							Object poiName = poiMember.get(POI_NAME_ + languages[j].toUpperCase());
							Object poiDetails = poiMember.get(POI_DETAILS_ + languages[j].toUpperCase());
							if (poiName != null)
							{
								preparedStmt4 = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestDescriptorsInsert);
								preparedStmt4.setString(1, poiCode);
								preparedStmt4.setBytes(2, ((String)poiName).getBytes("UTF-8"));
								if (poiDetails != null)
									preparedStmt4.setBytes(3, ((String)poiDetails).getBytes("UTF-8"));
								else
									preparedStmt4.setString(3, "");
								preparedStmt4.setString(4, languages[j]);
								preparedStmt4.executeUpdate();
								preparedStmt4.close();
								preparedStmt4 = null;
							}
						}
					}
					else //if points_of_interest table entry is to be updated
					{
						preparedStmt5 = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestUpdate);
						preparedStmt5.setDouble(1, poiLat);
						preparedStmt5.setDouble(2, poiLong);
						preparedStmt5.setString(3, poiCode);
						preparedStmt5.executeUpdate();
						preparedStmt5.close();
						preparedStmt5 = null;
						
						String[] languages = config.getLanguages();
						for (int j=0; j< languages.length; j++)
						{
							Object poiName = poiMember.get(POI_NAME_ + languages[j].toUpperCase());
							Object poiDetails = poiMember.get(POI_DETAILS_ + languages[j].toUpperCase());
							if (poiName != null)
							{
								preparedStmt5A = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestDescriptorsQuery);
								preparedStmt5A.setString(1, poiCode);
								preparedStmt5A.setString(2, languages[j]);
								rs2 = preparedStmt5A.executeQuery();
								if (rs2.next() == true)
								{
									preparedStmt6 = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestDescriptorsUpdate);
									preparedStmt6.setBytes(1, ((String)poiName).getBytes("UTF-8"));
									if (poiDetails != null)
										preparedStmt6.setBytes(2, ((String)poiDetails).getBytes("UTF-8"));
									else
										preparedStmt6.setString(2, "");
									preparedStmt6.setString(3, poiCode);
									preparedStmt6.setString(4, languages[j]);
									preparedStmt6.executeUpdate();
									preparedStmt6.close();
									preparedStmt6 = null;
								}
								else
								{
									preparedStmt6A = dbConnectionPool.getPreparedStmt(conn, pointsOfInterestDescriptorsInsert);
									preparedStmt6A.setString(1, poiCode);
									preparedStmt6A.setBytes(2, ((String)poiName).getBytes("UTF-8"));
									if (poiDetails != null)
										preparedStmt6A.setBytes(3, ((String)poiDetails).getBytes("UTF-8"));
									else
										preparedStmt6A.setString(3, "");
									preparedStmt6A.setString(4, languages[j]);
									preparedStmt6A.executeUpdate();
									preparedStmt6A.close();
									preparedStmt6A = null;
								}
								rs2.close();
								rs2 = null;
								preparedStmt5A.close();
								preparedStmt5A = null;
							}
						}
					}
					rs1.close();
					rs1 = null;
					preparedStmt2.close();
					preparedStmt2 = null;
				}
				else
				{
					throw new Exception("Invalid poi Info details in feed: " + "poiCode :" +poiCode + " poiLat: "+poiLat + " poiLong: "+poiLong);
				}
			} // end of 'for (int i=0; i<poiList.size(); i++)'
			conn.commit();
		}
 		catch (Exception e) 
 		{
 			System.out.println("Failed to update poiInfo in Database: "+ e);
 			e.printStackTrace();
 		}
 		finally
 		{
 			if (rs1 != null)
    		{
    			try {rs1.close();} catch (SQLException e) {} 
    			rs1= null;
    		}
 			if (rs2 != null)
    		{
    			try {rs2.close();} catch (SQLException e) {} 
    			rs2 = null;
    		}
    		if (preparedStmt1 != null)
    		{
    			try {preparedStmt1.close();} catch (SQLException e) {} 
    			preparedStmt1 = null;
    		}
    		if (preparedStmt2 != null)
    		{
    			try {preparedStmt2.close();} catch (SQLException e) {} 
    			preparedStmt2 = null;
    		}
    		if (preparedStmt3 != null)
    		{
    			try {preparedStmt3.close();} catch (SQLException e) {} 
    			preparedStmt3 = null;
    		}
    		if (preparedStmt4 != null)
    		{
    			try {preparedStmt4.close();} catch (SQLException e) {} 
    			preparedStmt4 = null;
    		}
    		if (preparedStmt5 != null)
    		{
    			try {preparedStmt5.close();} catch (SQLException e) {} 
    			preparedStmt5 = null;
    		}
    		if (preparedStmt5A != null)
    		{
    			try {preparedStmt5A.close();} catch (SQLException e) {} 
    			preparedStmt5A = null;
    		}
    		if (preparedStmt6 != null)
    		{
    			try {preparedStmt6.close();} catch (SQLException e) {} 
    			preparedStmt6 = null;
    		}
    		if (preparedStmt6A != null)
    		{
    			try {preparedStmt6A.close();} catch (SQLException e) {} 
    			preparedStmt6A = null;
    		}
			if (conn != null)
			{
				try {conn.close();} catch (SQLException e) {}
				conn = null;
			}
 		}
	}
}
